using SpreadsheetGear;
using Sustainalytics.Entities;
using Sustainalytics.Utils;
using Sustainalytics.Utils.Validation.Interfaces;
using System;

namespace Sustainalytics.Portfolios.BL
{
    using DataLayer.EsgRatings;
    using Entities.ESGRatings;
    using Sustainalytics.Portfolios.BL.Exceptions;
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;

    public class PortfolioSecuritiesImportManagement : IPortfolioSecuritiesImportManagement
    {
        private readonly IEsgInputFileRepository _esgInputFileRepository;
        private readonly ICustomPortfolioUpdateHandler _portfolioManager;
        private readonly IValidator<IWorksheet> _validator;

        public PortfolioSecuritiesImportManagement(
            IEsgInputFileRepository esgInputFileRepository,
            ICustomPortfolioUpdateHandler portfolioManager,
            IValidator<IWorksheet> validator)
        {
            _esgInputFileRepository = esgInputFileRepository;
            _portfolioManager = portfolioManager;
            _validator = validator;
        }

        public byte[] GetPortfolioSecuritiesTemplate(string fileName, string filePath)
        {
            var portfolioExportWorkBook = SpreadsheetGear.Factory.GetWorkbookSet().Workbooks.Open(filePath);

            return portfolioExportWorkBook.SaveToMemory(FileFormat.OpenXMLWorkbook);
        }

        public void SavePortfolioFile(string portfolioFilePath, PortfolioUpload portfolioUploadParam)
        {
            var fileName = portfolioUploadParam.PortfolioName + "_Company_Match.xlsx";
            var matrixFileContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";


            var workbook = SpreadsheetGear.Factory.GetWorkbookSet().Workbooks.Open(portfolioFilePath);
            var worksheetPortfolio = workbook.Worksheets["Portfolio"];

            if (!_validator.IsValid(worksheetPortfolio))
            {
                throw new InvalidPortfolioTemplate(portfolioUploadParam.PortfolioName);
            }

            FileStream stream = File.OpenRead(portfolioFilePath);

            byte[] portfolioFileContent = new byte[stream.Length];
            stream.Read(portfolioFileContent, 0, portfolioFileContent.Length);
            stream.Close();

            if (portfolioFileContent.Length == 0)
            {
                throw new ArgumentException("No file uploaded for portfolio processing (i.e. empty byte array)!");
            }

            _esgInputFileRepository.DeleteFiles(EsgInputFileType.Portfolio, portfolioUploadParam.PortfolioName, portfolioUploadParam.AccountId);
            _esgInputFileRepository.AddFile(new EsgInputFileComplete
            {
                Id = Guid.NewGuid(),
                AccountId = portfolioUploadParam.AccountId,
                Name = portfolioUploadParam.PortfolioName,
                Type = EsgInputFileType.Portfolio,
                FileName = fileName,
                Content = portfolioFileContent,
                ContentType = matrixFileContentType
            });
        }


        public byte[] GetPortfolioSecurities(string portfolioName, Guid accountId, Guid userId, string userName, string userFullName)
        {
            var portfolioFile = _esgInputFileRepository.GetESGFilesByFilter(x =>
                                                                            x.AccountId == accountId &&
                                                                            x.Type == EsgInputFileType.Portfolio &&
                                                                            x.Name == portfolioName);

            var workbook = SpreadsheetGear.Factory.GetWorkbookSet().Workbooks.OpenFromMemory(portfolioFile.Content);

            var worksheetPortfolio = workbook.Worksheets["Portfolio"];

            if (!_validator.IsValid(worksheetPortfolio))
            {
                throw new InvalidPortfolioTemplate(portfolioName);
            }

            AddHeaderForMatchingInfoToFile(worksheetPortfolio);

            var rowCount = worksheetPortfolio.UsedRange.RowCount;

            var portfolioMatches = _portfolioManager.GetPortfolioMatchesBy(portfolioName, accountId, userId);
            var portfolioMatchesDic = new Dictionary<string, IList<PortfolioMatches>>();

            foreach (var portfolioMatch in portfolioMatches)
            {
                if (portfolioMatchesDic.ContainsKey(portfolioMatch.DisplayInfo.SecurityIdentifier))
                    portfolioMatchesDic[portfolioMatch.DisplayInfo.SecurityIdentifier].Add(portfolioMatch);
                else
                    portfolioMatchesDic.Add(portfolioMatch.DisplayInfo.SecurityIdentifier, new List<PortfolioMatches>() { portfolioMatch });
            }

            // We could use foreach (IRange cell in usedRange) for cleaner
            // code, but this is faster.

            var rowIndex = 1;
            for (; rowIndex < rowCount; rowIndex++)
            {
                var securityIdentifier = GetColumnValue(worksheetPortfolio, rowIndex, ColumnSecurityIdentifierOffset);

                if (string.IsNullOrEmpty(securityIdentifier))
                {
                    continue;
                }

                if (!portfolioMatchesDic.ContainsKey(securityIdentifier))
                {
                    worksheetPortfolio.Cells[rowIndex, ColumnCompanyIQIdOffset].EntireRow.Delete();
                    rowCount--;
                    rowIndex--;
                    continue;
                }
                bool removeSecurity = false;

                WritePortfolioMatches(worksheetPortfolio, rowIndex, portfolioMatchesDic[securityIdentifier], out removeSecurity);

                if (removeSecurity) portfolioMatchesDic[securityIdentifier].RemoveAt(0);
            }

            foreach (var match in portfolioMatches.Where(x => x.AddedFromCompanySearch))
            {
                bool r;
                WritePortfolioMatches(worksheetPortfolio, rowIndex, new List<PortfolioMatches> { match }, out r);
                worksheetPortfolio.Cells[rowIndex, ColumnSecurityIdentifierOffset].Value = match.DisplayInfo.SecurityIdentifier;
                rowIndex++;
            }

            var result = workbook.SaveToMemory(FileFormat.OpenXMLWorkbook);

            workbook.Close();

            _portfolioManager.SaveMatchedPortfolioDetails(portfolioName, accountId, userId, userName, userFullName);

            return result;
        }

        public PortfolioImportResult SavePortfolioMatches(string filePath, PortfolioUpload portfolioUplaodParam, PortfolioUserClaims userClaims)
        {
            _portfolioManager.RemoveAllPortfolioMatches(portfolioUplaodParam.PortfolioName, userClaims.UserId, userClaims.AccountId);

            var workbook = SpreadsheetGear.Factory.GetWorkbookSet().Workbooks.Open(filePath);
            var worksheetPortfolio = workbook.Worksheets["Portfolio"];

            if (!_validator.IsValid(worksheetPortfolio))
            {
                throw new InvalidPortfolioTemplate(portfolioUplaodParam.PortfolioName);
            }

            var rowCount = worksheetPortfolio.UsedRange.RowCount;

            //load all the values in arrays for faster access; reading from the worksheet is slow
            object[,] securityNames = (object[,])worksheetPortfolio.Cells["A2:A" + rowCount].Value;
            object[,] securityIds = (object[,])worksheetPortfolio.Cells["B2:B" + rowCount].Value;
            object[,] securityWeights = (object[,])worksheetPortfolio.Cells["C2:C" + rowCount].Value;

            // close the workbook to save resources 
            workbook.Close();

            //limit the number of threads per process in order to prevent the CPU going to 100%

            /*var processorCount = Environment.ProcessorCount;
            var options = new ParallelOptions() { MaxDegreeOfParallelism = (int)(processorCount * 0.75) };*/

            var importMatches = new MatchesResult();

            // for synchronization purposes
            //object sync = new Object();

            // TO DO - use multiple treads to process the rows and in the end add the results in import matches
            //Parallel.For<MatchesResult>(0, rowCount - 1, options, () => new MatchesResult(), (rowIndex, loop, result) =>
            for (int rowIndex = 0; rowIndex < rowCount - 1; rowIndex++)
            {

                string securityName = Convert.ToString(securityNames[rowIndex, 0]);
                string securityId = Convert.ToString(securityIds[rowIndex, 0]);
                string securityW = Convert.ToString(securityWeights[rowIndex, 0]);
                double? securityWeight = null;
                double securityWeightParse;

                if (double.TryParse(securityW, out securityWeightParse))
                {
                    securityWeight = Math.Round(securityWeightParse, 7, MidpointRounding.AwayFromZero);
                }

                if (string.IsNullOrEmpty(securityId))
                {
                    importMatches.NumberOfUnmatchedRows++;
                }
                else
                {
                    var portfolioMatches = _portfolioManager.SearchForSecurities(securityName, securityId, userClaims);
                    var securites = _portfolioManager.AddMatchesAndPortfolio(portfolioUplaodParam.PortfolioName, portfolioUplaodParam.SharingStatus, portfolioMatches, securityWeight, userClaims);
                    importMatches.allSecurities.AddRange(securites);
                    foreach (var item in portfolioMatches)
                    {
                        if (item.IsValid)
                        {
                            importMatches.NumberOfCorrectMatches++;
                        }
                        else
                        {
                            importMatches.NumberOfUnmatchedRows++;
                        }
                    }

                }
            }

            /*
            return result;
            },
            (x) =>
            {
                lock (sync)
                {
                importMatches.NumberOfCorrectMatches += x.NumberOfCorrectMatches;
                importMatches.NumberOfUnmatchedRows += x.NumberOfUnmatchedRows;
                importMatches.allSecurities.AddRange(x.allSecurities);
            }
        }
        );*/

            _portfolioManager.SavePortfolioMatches(importMatches.allSecurities);
            var importResult = new PortfolioImportResult(rowCount - 1);

            importResult.NumberOfCorrectMatches = importMatches.NumberOfCorrectMatches;
            importResult.NumberOfUnmatchedRows = importMatches.NumberOfUnmatchedRows;

            importResult.FileContent = new byte[0];
            return importResult;
        }

        public class MatchesResult
        {
            public MatchesResult()
            {
                NumberOfUnmatchedRows = 0;
                NumberOfCorrectMatches = 0;
                allSecurities = new List<PortfolioMatches>();
            }
            public int NumberOfUnmatchedRows;
            public int NumberOfCorrectMatches;
            public List<PortfolioMatches> allSecurities;
        }

        private static string GetColumnValue(IWorksheet worksheetPortfolio, int row, int columnIndex)
        {
            if (worksheetPortfolio.Cells[row, columnIndex] != null)
            {
                return worksheetPortfolio.Cells[row, columnIndex].Text;
            }
            return string.Empty;
        }

        private const int ColumnSecurityNameOffset = 0;

        private const int ColumnSecurityIdentifierOffset = 1;

        private const int ColumnSecurityWeightOffset = 2;

        private const int ColumnCompanyNameOffset = 3;

        private const int ColumnCompanyIQIdOffset = 4;

        private const int ColumnDetailsOffset = 5;

        private static void AddWorksheetHeaderCell(IWorksheet worksheetPortfolio, int columnIndex, string text)
        {
            worksheetPortfolio.Cells[0, columnIndex].Value = text;
            worksheetPortfolio.Cells[0, columnIndex].Cells.Interior.Color = Color.FromArgb(0, 106, 158);
            worksheetPortfolio.Cells[0, columnIndex].Font.Bold = true;
            worksheetPortfolio.Cells[0, columnIndex].Font.Name = "Calibri";
            worksheetPortfolio.Cells[0, columnIndex].Font.Size = 10;
            worksheetPortfolio.Cells[0, columnIndex].Font.Color = Color.FromArgb(255, 255, 255);
        }

        private static void AddHeaderForMatchingInfoToFile(IWorksheet worksheetPortfolio)
        {
            AddWorksheetHeaderCell(worksheetPortfolio, ColumnSecurityWeightOffset, "Security Weight");
            AddWorksheetHeaderCell(worksheetPortfolio, ColumnCompanyNameOffset, "Matched Company name");
            AddWorksheetHeaderCell(worksheetPortfolio, ColumnCompanyIQIdOffset, "Matched Company ID");
            AddWorksheetHeaderCell(worksheetPortfolio, ColumnDetailsOffset, "Details");
        }

        private void WritePortfolioMatches(IWorksheet worksheet, int row, IList<PortfolioMatches> securities, out bool removeSecurity)
        {
            removeSecurity = false;
            worksheet.Cells[row, ColumnCompanyIQIdOffset].ColumnWidth = 26.19;
            worksheet.Cells[row, ColumnCompanyNameOffset].ColumnWidth = 46.19;
            worksheet.Cells[row, ColumnDetailsOffset].Style.WrapText = true;
            worksheet.Cells[row, ColumnDetailsOffset].ColumnWidth = 74.9;

            var validCount = securities.Count(x => x.IsValid);

            if (validCount == 0 && securities.Count() > 1)
            {
                var result = securities.GroupBy(x => x.CompanyId, x => x.MatchedInfo.CompanyName)
                    .Select(itemInGroup => itemInGroup.FirstOrDefault())
                    .Where(companyName => companyName != null)
                    .ToList();
                worksheet.Cells[row, ColumnDetailsOffset].Value = string.Join(", ", result);
                worksheet.Cells[row, ColumnCompanyIQIdOffset].Value = string.Empty;
                worksheet.Cells[row, ColumnCompanyNameOffset].Value = string.Empty;
            }
            else
            {
                var security = securities.First();
                worksheet.Cells[row, ColumnCompanyIQIdOffset].Value = security.MatchedInfo.CapitalIQId;
                worksheet.Cells[row, ColumnCompanyNameOffset].Value = security.MatchedInfo.CompanyName;
                worksheet.Cells[row, ColumnSecurityWeightOffset].Value = security.SecurityWeight;
                if (validCount > 1) removeSecurity = true;
            }
        }
    }
}